USE [VentaPorCatalogo]
GO


CREATE TABLE [dbo].[Divisiones](
	[IdDivision] [int] IDENTITY(1,1) NOT NULL,
	[Descripcion] [varchar](50) NULL,
 CONSTRAINT [PK_Divisiones] PRIMARY KEY CLUSTERED 
(
	[IdDivision] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE dbo.Zonas ADD
	IdDivision int NULL
GO

ALTER TABLE dbo.Zonas ADD CONSTRAINT
	FK_Zonas_Divisiones FOREIGN KEY
	(
	IdDivision
	) REFERENCES dbo.Divisiones
	(
	IdDivision
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Divisiones_i]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Divisiones_i]
GO

CREATE PROCEDURE [dbo].[Divisiones_i]
	@Descripcion varchar(50),
	@IdDivision int output	
AS
INSERT INTO Divisiones
           ([Descripcion])
     VALUES
           (@Descripcion)
SELECT @IdDivision =  SCOPE_IDENTITY()

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Divisiones_u]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Divisiones_u]
GO
CREATE PROCEDURE [dbo].[Divisiones_u]
@IdDivision int,
@Descripcion varchar(50)
AS
UPDATE Divisiones
   SET [Descripcion] = @Descripcion	   
 WHERE IdDivision = @IdDivision


GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Divisiones_d]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].Divisiones_d
GO
CREATE PROCEDURE [dbo].[Divisiones_d]  
	@IdDivision int  
AS  
DELETE FROM Divisiones
      WHERE IdDivision = @IdDivision


go



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Zonas_i]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Zonas_i]
GO

CREATE PROCEDURE [dbo].[Zonas_i]
	@Descripcion varchar(50),
	@IdDivision int,
	@IdZona int output	
AS
INSERT INTO Zonas
           ([Descripcion]
           ,[IdDivision]
           )
     VALUES
           (@Descripcion
           ,@IdDivision)
SELECT @IdZona =  SCOPE_IDENTITY()

GO




IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Zonas_u]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Zonas_u]
GO
CREATE PROCEDURE [dbo].[Zonas_u]
@IdZona int,
@IdDivision int,
@Descripcion varchar(50)
AS
UPDATE Zonas
   SET [Descripcion] = @Descripcion
	   ,[IdDivision] = @IdDivision	   
 WHERE IdZona = @IdZona

GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Zonas_d]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].Zonas_d
GO
CREATE PROCEDURE [dbo].[Zonas_d]  
	@IdZona int  
AS  
DELETE FROM Zonas
      WHERE IdZona = @IdZona


go





IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Areas_i]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Areas_i]
GO

CREATE PROCEDURE [dbo].[Areas_i]
	@Descripcion varchar(50),
	@IdZona int,
	@IdArea int output	
AS
INSERT INTO Areas
           ([Descripcion]
           ,[IdZona]
           )
     VALUES
           (@Descripcion
           ,@IdZona)
SELECT @IdArea =  SCOPE_IDENTITY()

GO




IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Areas_u]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Areas_u]
GO
CREATE PROCEDURE [dbo].[Areas_u]
@IdArea int,
@IdZona int,
@Descripcion varchar(50)
AS
UPDATE Areas
   SET [Descripcion] = @Descripcion
	   ,[IdZona] = @IdZona	   
 WHERE IdArea = @IdArea

GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Areas_d]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].Areas_d
GO
CREATE PROCEDURE [dbo].[Areas_d]  
	@IdArea int  
AS  
DELETE FROM Areas
      WHERE IdArea = @IdArea


go


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Zonas_GetAllPaginated]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].Zonas_GetAllPaginated
GO

CREATE PROCEDURE [dbo].[Zonas_GetAllPaginated]
	@IdZona int = NULL,        
	@IdDivision int = NULL,        
	@Descripcion varchar(50) = NULL,        
	@PageNum int = NULL,            
	@PageSize int = NULL,          
	@PageCount int output            
AS          
SELECT         
 IdZona,         
 IdDivision,
    Descripcion  
FROM  ( SELECT Z.IdZona,Z.IdDivision,Z.Descripcion,ROW_NUMBER()          
  OVER (ORDER BY Z.Descripcion) AS RowNumber           
  FROM Zonas Z  
  WHERE (IdZona  = @IdZona  or @IdZona is null) AND           
		(IdDivision = @IdDivision or @IdDivision is null) AND           
        (Z.Descripcion like '%' +  @Descripcion + '%' or @Descripcion is null)) AS Page          
WHERE RowNumber           
  BETWEEN (@PageSize * @PageNum + 1) AND (@PageSize * (@PageNum + 1))          
  or @PageNum is null    
            
select @PageCount=COUNT(*)          
  FROM Zonas Z  
  WHERE (IdZona  = @IdZona  or @IdZona is null) AND           
		(IdDivision = @IdDivision or @IdDivision is null) AND           
        (Z.Descripcion like '%' +  @Descripcion + '%' or @Descripcion is null)
        
        
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Divisiones_GetAll]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].Divisiones_GetAll
GO
CREATE PROCEDURE [dbo].[Divisiones_GetAll]
AS
SELECT [IdDivision]
      ,[Descripcion]
  FROM Divisiones

GO